Good drivers,
I now have a demand, within 90 days to query a merchant whether have order, I have found two implementations, one is to use the exists, one kind is with the count, don't know what kind of better, old can help analyze the driver?

By using the exists a:

 
SELECT 1 FROM dual WHERE the EXISTS (SELECT * FROM T_ORDER WHERE customer_no='1111111' 
And create_time & gt; To_date (' 2017-10-24 00:00:00 ', '- dd yyyy - mm hh24: mi: ss') 
And create_time & lt; 23:59:59 to_date (' 2018-01-24 ', '- dd yyyy - mm hh24: mi: ss') 
); 

The way using count:

 

SELECT count (1) the from T_ORDER where customer_no='1111111' 
And create_time & gt; To_date (' 2017-10-24 00:00:00 ', '- dd yyyy - mm hh24: mi: ss') 
And create_time & lt; 23:59:59 to_date (' 2018-01-24 ', '- dd yyyy - mm hh24: mi: ss'); 

Below is the test data, eliminating the various indexes of what,

 

Drop table "T_ORDER"; 

CREATE TABLE "T_ORDER (
""ID" INT the NOT NULL, 
"CUSTOMER_NO" VARCHAR2 (30) NULL, 
"CREATE_TIME" DATE NULL 
); 

INSERT INTO "T_ORDER" (" ID ", "CUSTOMER_NO", "CREATE_TIME") VALUES (' 1 ', '1111111', TO_DATE (' 2018-01-24 11:25:02 ', 'SYYYY - MM - DD HH24: MI: SS')); 
INSERT INTO "T_ORDER" (" ID ", "CUSTOMER_NO", "CREATE_TIME") VALUES (' 2 ', '1111111', TO_DATE (' 2018-01-08 11:25:15 ', 'SYYYY - MM - DD HH24: MI: SS')); 
INSERT INTO "T_ORDER" (" ID ", "CUSTOMER_NO", "CREATE_TIME") VALUES (' 3 ', '1111111', TO_DATE (' 2018-01-01 11:25:24 ', 'SYYYY - MM - DD HH24: MI: SS')); 

CodePudding user response:

Use way two, from the point of execution plan, both should be the same, is inherently involves only a single standard, it is not necessary to the entire table more the correlation method and way of this kind of writing is not recommended,

Should have on customer_no index, if there are customer_no, create_time composite index performance is better,
Attention should be paid to customer_no this field value to and the field type is consistent, avoid implicit conversion to go full table,

CodePudding user response:

reference 1st floor liuzhijian2008x response:

use way two, from the point of execution plan, both should be the same, is inherently involves only a single standard, it is not necessary to the entire table more the correlation method and way to this kind of writing is not recommended,

Should have on customer_no index, if there are customer_no, create_time composite index performance is better,
Attention should be paid to customer_no this field value to and the field type is consistent, avoid implicit conversion to go full table,

Well, customer_no and create_time single index, but there is no combination index, you said "don't need the whole table more related spelled" this on?

CodePudding user response:

refer to the second floor yuyujulin response:

Quote: refer to 1st floor liuzhijian2008x response:

Use way two, from the point of execution plan, both should be the same, is inherently involves only a single standard, it is not necessary to the entire table more the correlation method and way of this kind of writing is not recommended,

Should have on customer_no index, if there are customer_no, create_time composite index performance is better,
Attention should be paid to customer_no this field value to and the field type is consistent, avoid implicit conversion to go full table,

Well, customer_no and create_time single index, but there is no combination index, you said "don't need the whole table more related spelled" this on?

//
Execution plan to see the same, can simple SQL, of course, there's no need to get into the complicated SQL, SQL optimizer analysis must be simple faster and faster, it is concluded that the execution of the plan are more depend on spectrum,

CodePudding user response:

This query is the key to T_ORDER queries, to establish customer_no and create_time composite index, and the customer for the index in the first column, create_time as index column 2, single index can only use one of these, screening of sex is bad,

CodePudding user response:

User table redundancy a field "order time" at the end, the user after place an order, update the field

CodePudding user response:

reference 5 floor kingkingzhu reply:

user table redundancy a field "order time" at the end, the user after place an order, update the field under

idea is very good, but now no more to add this also does not display, light in terms of the two SQL, which is more efficient?

CodePudding user response:

In the case of large amount of data, the first efficiency should be higher, because if there is order, it will return; And use the count function, under the condition of large amount of data, is very impact performance

CodePudding user response:

In the case of large amount of data, the first efficiency should be higher, because if there is order, it will return; And use the count function, under the condition of large amount of data, is very impact performance

CodePudding user response:

Large amount of data to recommend the first one, on the other hand the second, and each field has a single index, where conditions have combination index in the index order write back, create_time & gt; , & lt; Writing is not to walk index, can be written as & gt;=, & lt;=

CodePudding user response:

references 9 f u011210161 response:

large amount of data the first recommendation, whereas the second, and each field has a single index, where conditions have combination index in the index order write back, create_time & gt; , & lt; Writing is not to walk index, can be written as & gt;=, & lt;=

Even if the large amount of data, I added a filter conditions in this statement, also left index, is unlikely to be what you said this

CodePudding user response:

Before all, to look again, you this two statements in general sql1 performance is high, because sql1 is half as long as there are returned, sql2 is need to calculate how much is this data,
Business logic you just need to know that there does not exist, don't need to know how many exists, sql1 higher overall performance

CodePudding user response:

references to the tenth floor yuyujulin response:

Quote: references 9 f u011210161 response:

Large amount of data to recommend the first one, on the other hand the second, and each field has a single index, where conditions have combination index in the index order write back, create_time & gt; , & lt; Writing is not to walk index, can be written as & gt;=, & lt;=

Even if the large amount of data, I added a filter conditions in this statement, also left index, there should be no is you said this

Didn't add function on the field, this should be going range index,

CodePudding user response:

refer to 12 floor baidu_36457652 reply:

Quote: reference to the tenth floor yuyujulin response:

Quote: references 9 f u011210161 response:

Large amount of data to recommend the first one, on the other hand the second, and each field has a single index, where conditions have combination index in the index order write back, create_time & gt; , & lt; Writing is not to walk index, can be written as & gt;=, & lt;=

Even if the large amount of data, I added a filter conditions in this statement, also left index, there should be no is you said this

Didn't add function on the field, this should be going range index,

null