SQL & PL/SQL

Hi experts,

I recently joined this organization as a junior developer in PL/SQL. I have a question on the way the tables have been designed, wondering if its just my lack of knowledge, so please help.

I noticed that here two tables are being used, almost identical, one which is partitioned and the other is not (say T1 and T2). The non-partitioned table T2 has around 230 columns, all indexed (295 indexes in total). All are bitmap indexes, either normal or function-based.
The partitioned table T1 has around 300 extra columns (in addition to the 230 columns in T2), and this is something like a detail table, while T2 is the summary table.
Two main types of queries are issued from the user end - One for retrieving the count for certain parameters, the other for getting actual data for report generation. The requirement specifies that any query for retrieving the counts must give instantaneous results, while those requiring all data to be pulled are allowed to take some time.

So SQL Queries are usually issued against T2, and if only counts are to be retrieved, data is pulled from T2. If reports need to be generated with actual data, then based on the columns searched, the tables are joined & the necessary iinformation is retrieved from T1.

When I asked the existing developers about the need to have 2 separate tables (where one is a pure subset of the other), the explanation I got was --
Table T1 is really big and is refreshed with millions of records daily, so it should be partitioned. But Oracle does not allow creating Bitmap indexes on Partitioned tables, so T2 has been created with bitmap indexes against which queries may run for quick results, ie in case of counts. If bitmap indexes & partitioned tables could go together, we would have easily done away with table T2.

This was news to me, and I read from the following link that partitioned tables can indeed have bitmap indexes, but only local ones -

http://www.lorentzcenter.nl/awcourse/oracle/server.920/a96520/indexes.htm#745

I am not very familiar with local & global indexes(it wasn't clear to me from the link either), so please tell me how exactly they differ and whether there are any performance issues using local indexes.
I just want to know if I am wrong if I suggest the use of one table instead of two, given their insistence in using bitmap indexes for getting the query results.

Thanks in advance.

Edited by: user10874133 on Jan 30, 2009 2:21 AM

Sorry forgot to add, Oracle version we are using is - 10.2.0.3.0