CODEWITHSUNDEEP
sqldatabaseoracle-databaseoptimizationquery-optimization
Károly Neue
Károly Neue

Reputation: 99

How can I optimize an SQL query (Using Indexes but not bitmap indexes)?

I have an SQL query, example:

SELECT * FROM TAB1 NATURAL JOIN TAB2 WHERE TAB1.COL1 = 'RED'

How can I optimize this query to use indexes but not bitmap indexes in Oracle?

Upvotes: 0

Views: 162

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

NOTE: This answers the original version of the question.

First, don't use NATURAL JOIN. It is an abomination because it does not use properly declared foreign key relationships. It simply uses columns with the same name, and that can produce misleading results.

Second, the query is syntactically incorrect for two reasons. First, "Red" is a reference to a column, not a string value. Does the table have a column named "Red". The second reason is that you have a self join, so ROW1 is ambiguous.

That rings up the larger issue. Your query basically makes no sense at all. You are joining the table to itself, returning duplicate columns. What are the results? Pretty indeterminate:

  • If any column contains a NULL value, then no rows are returned.
  • If all the rows are duplicates (with no NULL values), then you'll get a result set with the N^2 rows and duplicate columns, where N is the number of rows in the table.

I cannot think of any use for the query. I see no reason to try to optimize it.

If you have a real query that you want to discuss, I would suggest that you ask another question.

Upvotes: 1

Related Questions