Oracle Database Discussions

Hello

I just want to verify with the community if my setup for using Bitmap Join Indexes and Partitioning makes sense in my specific situtation.

I have a table 'job' containing job loads
The table 'doc' contains the actual data, each row references the id from the job table

The 'job' table will contain after a year 5000 entries.
Each job will have 1000 - 50000 rows saved in 'doc' making more than 50'000'000 rows.

Tables
------

I simplified the tables:

CREATE TABLE JOB
(
ID NUMBER NOT NULL,
TIMESTAMP_INS date default sysdate
)

CREATE TABLE DOC
(
ID NUMBER NOT NULL,
job_id NUMBER NOT NULL, -- foreign key job.id
s1 VARCHAR2(100),
s2 VARCHAR2(100)
)

Selecting data
--------------

The application will ONLY retrieve data with the following select.
Please notice that the select ALWAYS goes via the 'job' table to the 'doc' table to retrieve the data.

select d.*
from job j
,doc d
where j.id = :1 -- OR j.id in (1, 2, 3)
and d.job_id = j.id


Bitmap Join Indexes
-------------------

For performance tuning I made the following Bitmap Join Indexes:

CREATE BITMAP INDEX doc_job_bjix
ON doc(job.id)
FROM doc, job
WHERE doc.job_id = job.id
LOCAL NOLOGGING COMPUTE STATISTICS;

Q1: Is this OK ?
Q2: Do I need seperate bitmap indexes on the job and doc table ?

create bitmap index xxx on job(id);
create bitmap index xxx on doc(job_id);

Partitioning
------------

CREATE TABLE DOC
(
ID NUMBER NOT NULL,
job_id NUMBER NOT NULL, -- foreign key job.id
s1 VARCHAR2(100),
s2 VARCHAR2(100)
)
TABLESPACE U3
PARTITION BY HASH(job_id) PARTITIONS 16;


Q3: As explained before, we always select doc data via a join on the job table.
Does it make sense to partition the doc table as shown above ?


Thanks for your help