CODEWITHSUNDEEP
sqldatabaseoracle-database
WooseokChoi
WooseokChoi

Reputation: 21

Does index still exists in these situations?

I have some questions about index.

First, if I use a index column in WITH clause, dose this column still works as index column in main query?

For example,

WITH TEST AS (
SELECT EMP_ID
FROM EMP_MASTER
)

SELECT *
FROM TEST
WHERE EMP_ID >= '2000'

'EMP_ID' in 'EMP_MASTER' table is PK and index for EMP_MASTER consists of EMP_ID.

In this situation, Does 'Index Scan' happen in main query?

Second, if I join two tables and then use two index columns from each table in WHERE, does 'Index Scan' happen?

For example,

SELECT *
FROM A, B
WHERE A.COL1 = B.COL1 AND
      A.COL1 > 200 AND
      B.COL1 > 100

Index for table A consists of 'COL1' and index for table B consists of 'COL1'.

In this situation, does 'Index Scan' happen in each table before table join?

If you give me some proper advice, I really appreciate that.

Upvotes: 0

Views: 50

Answers (2)

Dai
Dai

Reputation: 155578

First, if I use a index column in WITH clause, dose this column still works as index column in main query?

Yes. A CTE (the WITH part) is a query just like any other - and if a query references a physical table column used by an index then the engine will use the index if it thinks it's a good idea.

In this situation, Does 'Index Scan' happen in main query?

We can't tell from the limitated information you've provided. An engine will scan or seek an index based on its heuristics about the distribution of data in the index (e.g. STATISTICS objects) and other information it has, such as cached query execution plans.

In this situation, does 'Index Scan' happen in each table before table join?

As it's a range query, it probably would make sense for an engine to use an index scan rather than an index seek - but it also could do a table-scan and ignore the index if the index isn't selective and specific enough. Also factor in query flags to force reading non-committed data (e.g. for performance and to avoid locking).

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270773

First, SQL is a declarative language, not a procedural language. That is, a SQL query describes the result set, not the specific processing. The SQL engine uses the optimizer to determine the best execution plan to generate the result set.

Second, Oracle has a reasonable optimizer.

Hence, Oracle will probably use the indexes in these situations. However, you should look at the execution plan to see what Oracle really does.

Upvotes: 2

Related Questions