let us
suppose there are two tables M and P.
both Contain the
field emp_id. other columns may be different.
All records of M
also Exist in P .Table M will have records in the range 1-5
lakhs.
P
table will contain Additional Records such
that the Total Number of Records in P is 15-20 times the number of
records in M.
one way to
join the two tables is to say M.emp_id = P.emp_id. but because P has high number
of records the select is slower.
we
found that select of a row from table "P"
using "rowid" column was very QUICK .
Is it a Correct practice
:-
1) to Store the ROWID of Table P
in M in a separate column (say "P_rowid")
2) Is it possible
to do a Join like the follows :-
select
field1, field2,... from M,P
where M.emd_id = '6223'
and <like M.P_rowid = P.rowid>
this way we
hoped to select from M table (which has less number of records) and do a rowid
based select on P table, which we found out is not allowed by ORACLE.
we want a
join because we want to create a view over table M and P.
We do NOT want to use the following way
:-
select
field1, field2 ..,P_rowid from M where emp_id = '6223' from a Cursor & passing it to the Query as
follows :-
select *
from P where rowid = P_rowid (selected earlier)
Are there Some Standard Practices that Should be
Followed during Designing Tables , Fields, SQL Writing
?
Any Dos , Don'ts ?
