SQL query, =, IN, OR, index, sqlor

SQL query, =, IN, OR, index, sqlor

SELECT c FROM t WHERE c = 1

SELECT c FROM t WHERE c in (1)

Or

SELECT c FROM t WHERE c = 1 OR c = 2

SELECT c FROM t WHERE c in (1, 2)

IN the preceding SQL statements, the first (=, IN) and the second (=, OR, IN) use the same execution plan, the same index is also used in the execution plan, and the logical reads in the disk activity information are the same.

Set statistics io on enables SQLSERVER to display disk activity information generated by the Transact-SQL statement.
Set statistics time on displays the number of milliseconds required for analysis, compilation, and statement execution
CHECKPOINT forces all dirty pages of the current database to be written to the disk, and then clears the buffer.
Dbcc dropcleanbuffers deletes all CleanBuffers from the buffer pool

Whether the IN index is used IN MSSQL

Note: The following statement is used to compile the clustered index of the Table [Table] field [id] int primary key: SELECT * FROM [Table] WHERE id = 1. You must use the index. Let's look at the following three statements: SELECT * FROM [Table] WHERE id = 1 or id = 2 SELECT * FROM [Table] WHERE id IN (1, 2) SELECT * FROM [Table] WHERE id = 1 union select * FROM [Table] WHERE id = 2 I can tell you that indexes are used. The second method is MSSQL automatically optimized to: id = 1 or id = 2, instead of scanning the entire Table: SELECT * FROM [Table] WHERE id not in (1, 2) I can also tell you how to use indexes. Speaking of this, I have to say that a lot of SQL optimization materials are too old, and mssql2 will be improved later. It refers to the Execution Plan and the logical reading of IO reads and writes: set statistics io on/OFFSET SHOWPLAN_ALL ON/OFF. First, write a bit. No index is required for subqueries. Please shoot bricks ...... Note: No one gave me a final conclusion, so I believe that I am as follows: table [table] field [id] PRIMARY KEYMSSQL2005 by default: index is used in the following statements: SELECT * FROM [table] WHERE id IN (1, 2) SELECT * FROM [table] WHERE id = 1 OR id = 2 SELECT * FROM [table] WHERE id not in (1, 2) SELECT * FROM [table] WHERE id = 1 UNIONSELECT * FROM [table] WHERE id = 2 SELECT * FROM [table] WHERE id IN (select id from [table_other] WHERE...) only the following one does not need to be indexed:

If you run an SQL query in oracle, will the fields in the where condition in () start indexing?

It depends on whether the oracle optimizer chooses to use the INDEX. This depends on the uniqueness and distribution of the data in the name column of your table. There are two possible cases.
1. the uniqueness of name is poor:
('Name1', 'name2', 'name3', 'name4', 'name5') the percentage of data accessed by the condition is large, the total cost of index access is greater than the cost of full table scan. At this time, the optimizer will select full table scan, that is, the index will not be used.
2. The name is unique.
('Name1', 'name2', 'name3', 'name4', 'name5') the percentage of data accessed by the condition is small, in this case, the optimizer will choose to use the INDEX, so the performance is higher than that without the INDEX.

Note: The ORACLE optimizer understands that table data distribution depends on statistical information. Therefore, accuracy of statistical information is very important. Otherwise, incorrect selection may occur, leading to performance degradation.