indexing and joins--what happens

From: mr_oatmeal <mr_oatmeal_at_hotmail.com>
Date: Mon, 18 Dec 2000 23:30:03 -0000
Message-ID: <t3t7frlvt1h28c@corp.supernews.com>

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?

(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.

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?

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?

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 Mon Dec 18 2000 - 17:30:03 CST