Re: indexing and joins--what happens

mr_oatmeal wrote:

> Hello,
>
> I have a question, but no DBA to ask...hopefully someone will pick this up.
> --I'm on 8i (8.1.5)
> --i don't know what type of analyzer is set up, assuming is cost based?
>

SQL> select value from v$parameter where name = 'optimizer_mode';

OR

SQL> show parameters
and look for the optimizer_mode value manually

Both will probably tell you that your DB is set to CHOOSE. And yes, this means the Cost-Based Optimizer is taking over. However, unless your e and s tables have been analyzed, the CBO doesn't have any "smarts" to work from to determine the best paths to take when retrieving datasets to satisfy your SQL.

The syntax for that is:
SQL> analyze table <tablename> compute statistics;

However, is your e and s tables are huge (hundreds of millions of rows long), compute may take too long. In that case use:

SQL> analyze table <tablename> estimate statistics sample 20 percent;

>
> (feel free to answer any of the below questions)
 

>
> Question 1
> I'm joing two tables e to s, both have their own concatenated indexes.
> What I wanted to know, is what happens in the join, do their indexes get
> used or does some method come into play for choosing how the data will be
> selected.
>

You would need to look at the Explain Plan information for your query. If you haven't yet, go to www.toadsoft.com and download/install the freeware version of that program. It will automatically discover your existing connections to Oracle databases. Have your DBA run the toadprep.sql script. Then connect to a DB using your account, write or paste a SQL statement in the SQL window and then click the little ambulance icon, or use Ctrl+E to explain the statement. I've done explain plans for years by hand (which you can learn how to do reading the Oracle performance tuning documentation). I've found this tool to be the easiest way to get my plans. The best part is how easy it is to get a plan without actually running the statement (important in large DB environments). Another easy way (assuming your DBA read his Oracle docs and set stuff up properly) is to just type

SQL> set autotrace on

Any SQL statement you type into SQL*Plus beyond that point will give you the explain plan, as well as execution statistics. I find the db block gets, physical reads and disk sorts to be good indicators of SQL statements or tables that need to be tuned.

>
> Question 2
> Will this select statement even use an index due clause that use NULL? If
> used a hint to explicitly call the index, would the use of NULL still kill
> my index?
>

If I understood the question, no. If your where clause includes a phrase like: s.column1 is null, I believe it skips any existing indexes and does a full table scan. Keep in mind that a full table scan can actually be faster than indexed access; it depends on the percentage of the table you'll be retrieving. The rule of thumb varies between 5 and 15% as the high-end before full table scans are actually more efficient. Explicitly? Hmm. I'd have to try it. I believe it might ignore your hint anyway.

A nullable column that is sparsely populated and is indexed can be efficient in a where clause if you are searching for rows where the column is not null. This is due to the small size of the index itself, and the minimal matches that need to be retrieved.

>
> Question 3
> I am trying to add indexes to our tables and was wondering if it would be
> a good idead to add a bitmapped index to table e and s to use on the
> fields that use that have 0 or 1 values in addition to my concatenated
> index?
>

That depends. If the table is updated, deleted or inserted to frequently, absolutely not. Bitmapped indexes are beautiful things. But they grow OUT OF CONTROL unless it is a table that is, say, updated nightly or weekly and the indexes are rebuilt at the end of each refresh.

• bill c.

>
> THANKS,
> mr_oatmeal
>
> Below is the select stmnt that has the join. Table e has a concat index
> of enity_code, version_index. Table s has a concat index of entity_code,
> version_index, sector_code.
>
> SELECT e.entity_code,
> e.version_index,
> e.headline,
> e.hook,
> to_char(e.publication_date,'Mon dd, YYYY HH:MI pm') pubdate,
> nvl(e.is_updated, 0) is_updated,
> nvl(e.is_exclusive, 0) is_exclusive,
> NVL(e.is_in_home_page, 0) homepage,
> s.sector_code
> FROM e
> s
> WHERE e.entity_code = s.entity_code
> AND e.version_index = s.version_index
> AND e.data_entity_code = 1
> AND s.is_primary_sector = 1
> AND e.published = 1
> AND e.is_latest_version = 1
> AND e.publication_date is not null
> AND e.publication_date <= SYSDATE
> AND ((e.deleted <> 1) OR (e.deleted IS NULL))
> ORDER BY e.is_in_home_page desc, publication_date desc
>
> --
> Posted via CNET Help.com
> http://www.help.com/
Received on Tue Dec 19 2000 - 00:44:20 CST