| indexes [message #136667] |
Fri, 09 September 2005 08:12  |
adi_dba
Messages: 4
Registered: September 2005
Location: Delhi
|
Junior Member |
|
|
|
Can we create an index on columns of two or more tables? Please answer as soon as possible. its vry urgent for me to know.....
|
|
|
|
|
|
|
|
| Re: indexes [message #136762 is a reply to message #136699] |
Fri, 09 September 2005 23:55  |
adi_dba
Messages: 4
Registered: September 2005
Location: Delhi
|
Junior Member |
|
|
we can create a index on a table's column for optimization of the query:
like if we have a table emp with columns:
emp_no,
emp_name,
emp_salary,
dept_no.
So, we can build index on any of the above columns.
lets have another table Dept having following columns:
dept_no,
dept_name,
location.
i can build index on any of the column of dept table.
right!!
i fired a query:
"select emp_no,e.dept_no,d.dept_name from emp e,dept d where e.dept_no=d.dept_no;"
Now my question is that can we build an index on "e.dept_no" and "d.dept_no" together.
Hopefully now you wud have understood my problem. If hav any related answer plz reply soon.
thanks.
|
|
|
|
|
|
| Re: indexes [message #136774 is a reply to message #136766] |
Sat, 10 September 2005 01:31 |
adi_dba
Messages: 4
Registered: September 2005
Location: Delhi
|
Junior Member |
|
|
CANN'T I BUILD AN INDEX ON BOTH THE COLUMNS EMP.DEPT_NO AND DEPT.DEPT_NO SIMULTANEOUSLY????
ACTUALLY I HAD READ SOMETHING ABOUT THIS POOSSIBILTY LONG BACK BUT, DN'T REMEMBER NOW WHERE EXACTLY I READ THIS????
|
|
|
|
|
|
| Re: indexes [message #136947 is a reply to message #136808] |
Mon, 12 September 2005 07:39 |
SoporteDBA
Messages: 7
Registered: July 2005
Location: Écija, Sevilla
|
Junior Member |
|
|
Hello,
There is a new feature on Oracle9i called "Bitmap Join Indexes" that could do what you are talking about:
From Oracle documentation:
create table emp1
as select * from scott.emp;
create table dept1
as select * from scott.dept;
alter table dept1
add constraint dept_constr1 unique (deptno);
create bitmap index empdept_idx
on emp1(dept1.deptno)
from emp1, dept1
where emp1.deptno = dept1.deptno;
select /*+ index(emp1 empdept_idx) */ count(*)
from emp1, dept1
where emp1.deptno = dept1.deptno;
COUNT(*)
-------------
14
Elapsed: 00:00:00.67
Execution Plan
---------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 BITMAP CONVERSION (COUNT)
3 2 BITMAP INDEX (FULL SCAN) OF 'EMPDEPT_IDX'
|
|
|
|
| Re: indexes [message #136961 is a reply to message #136667] |
Mon, 12 September 2005 08:30 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I was thinking about the bitmap join index also, which is why I wanted to make sure the question was clear.
But it should be mentioned that this is a technique for use in DSS or warehouse type systems and is almost certainly going to be a bad idea if you are on a typical oltp system doing modifications to your emp and dept table. Bitmap indexes don't like modifications.
So, if you data changes, you may consider building 2 separate btree indexes, one each on deptid and empid. Although in this case, if you create typical constraints such that you had a PK constraint on both columns, then oracle would have already created a unique btree on each column for you.
Then oracle can (potentially if the CBO thinks its best) use both indexes to aid your query. For example, it might range or fast full scan each index individually, then hash join the results of those scans together.
If you are running oltp, I'd think this should be the goal you strive for.
|
|
|
|
| Re: indexes [message #140078 is a reply to message #136961] |
Fri, 30 September 2005 14:08 |
Hindustanweb
Messages: 2
Registered: September 2005
Location: Orlando
|
Junior Member |
|
|
Yes. We did same thing for our DSS Healthcare database and works really good with bitmap join index.
|
|
|
|
|
|
| Re: indexes [message #140419 is a reply to message #136667] |
Tue, 04 October 2005 03:10 |
MauriceM
Messages: 7
Registered: June 2005
|
Junior Member |
|
|
Depending for what you need it you may create an MV joining the two tables and then create an index over the MV. You could also try to create this MV based on an IOT table.
BUT this solution depends on how often your data get updated and for what you exactly need you index.
Also it would be good to know which Oracle version you are using.
[Updated on: Tue, 04 October 2005 03:12] Report message to a moderator |
|
|
|
| Re: indexes [message #140725 is a reply to message #140419] |
Wed, 05 October 2005 07:03 |
adi_dba
Messages: 4
Registered: September 2005
Location: Delhi
|
Junior Member |
|
|
WHAT DO YOU MEAN BY "MV JOINING" AND "IOT TABLES" ?
PLZ TELL.
THANX IN ADVANCE.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
