Current Position:Home > Querying wide (multi-record case) table data in oracle 10g

Querying wide (multi-record case) table data in oracle 10g

Update:10-11Source: network consolidation
Advertisement
I've got a set of "wide" (multi-record case data format) tables similar to the format described in Oracle's "Oracle Data Mining Concepts" book in chapter 2 (http://download-west.oracle.com/docs/cd/B14117_01/datamine.101/b10698/2data.htm#1010394).
I like the flexibility of this format, especially since it can handle thousands of attributes. But the main problem I have with this format is in determining how to write efficient, complex queries against the data.
I've included some sample data below to demonstrate my problem. I've got two tables, one with people and the other with the attributes and values of age, income, height and weight. If I try to generate a complex query against this data, I seem to either have to rely on multisets and functions, or on a large number of self joins. Neither of which seems to scale well or perform well for large amounts of data. I know I could also add a pipelined TABLE function to filter the data more, but none of these options seem like great solutions. Does anyone have any ideas for a better way to form complex well performing queries against this type of data layout?
For example, with my sample data below I'd like to select everyone whose (income = 2000) or (income = 50000 and weight = 210), but the only way I see of doing this is with a query like the following (which won't take advantage of any indexes on the data):
select * from people_attributes_view
where find_eq_attr(AttributeValue('income',2000),attribute_values) is not null
or (find_eq_attr(AttributeValue('income',50000),attribute_values) is not null
and
find_eq_attr(AttributeValue('weight',210),attribute_values) is not null);
Any help is greatly appreciated.
Thanks.
-- My full example starts here ----------------
-- Create the sample tables and data
create table people (id int, name varchar(30));
create table attributes (id int, attribute varchar(10), value NUMBER);
insert into people values (1,'tom');
insert into people values (2,'jerry');
insert into people values (3,'spike');
commit;
insert into attributes values (1,'age',23);
insert into attributes values (1,'income',1000);
insert into attributes values (1,'height',5);
insert into attributes values (1,'weight',120);
insert into attributes values (2,'age',20);
insert into attributes values (2,'income',2000);
insert into attributes values (3,'age',30);
insert into attributes values (3,'income',50000);
insert into attributes values (3,'weight',210);
commit;
-- Create some types, functions and views for the search
CREATE OR REPLACE TYPE AttributeValue AS OBJECT
(attribute varchar(30),
value NUMBER);
CREATE OR REPLACE TYPE AttributeValues AS TABLE OF AttributeValue;
create or replace function find_eq_attr (val AttributeValue, vals AttributeValues) RETURN AttributeValue IS
begin
for i in vals.FIRST .. vals.LAST
LOOP
if ((val.attribute = vals(i).attribute) and
(val.value = vals(i).value)) then
return vals(i);
end if;
END LOOP;
return null;
end;
create or replace view people_attributes_view as
select p.name, p.id,
cast(multiset(select attribute,value from attributes a where a.id = p.id)
as AttributeValues) attribute_values
from people p;
-- Search for everyone whose (income = 2000) or (income = 50000 and weight = 210)
select * from people_attributes_view
where find_eq_attr(AttributeValue('income',2000),attribute_values) is not null
or (find_eq_attr(AttributeValue('income',50000),attribute_values) is not null
and
find_eq_attr(AttributeValue('weight',210),attribute_values) is not null);

The Best Answer

Advertisement
I like the flexibility of this format, especially
since it can handle thousands of attributes. But the
main problem I have with this format is in
determining how to write efficient, complex queries
against the data. Can't be done. The flexibility is achieved at the cost of simplicity and performance, it is a trade off.
See this thread for a full discussion on the subject.
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:10678084117056
  • Querying wide (multi-record case) table data in oracle 10g Update:10-11

    I've got a set of "wide" (multi-record case data format) tables similar to the format described in Oracle's "Oracle Data Mining Concepts" book in chapter 2 (http://download-west.oracle.com/docs/cd/B14117_01/datamine.101/b10698/2data.ht

  • How to transfer data between different tables in an Oracle 10g databse? Update:11-30

    I have to do the following: there are 5 database tables in our Oracle 10g database that store certain data. Then there are 2 new tables where we want the data to go to. My task is to somehow transfer all the data that is in the first 5 tables and ins

  • Give me the sql query which calculte the table size in oracle 10g ecc 6.0 Update:11-30

    Hi expert, Please  give me the sql query which calculte the table size in oracle 10g ecc 6.0. RegardsOrkun Gedik wrote: select segment_name, sum(bytes)/(1024*1024) from dba_segments where segment_name = '<TABLE_NAME>' group by segment_name; Hi, This

  • What is the easiest way to export all tables data from Oracle to MS SQL Server? Update:10-11

    Hello MS, I would like to export all tables from Oracle 11.2 to MS SQL Server 2012 R1. Using the tool "Microsoft SQL Server Migration Assistant v6.0 for Oracle" did not work for me because there are too many warnings and errors regarding the sch

  • Error in import table data using oracle datapump Update:10-11

    i am trying to import table data using oracle datapump CREATE TABLE emp_xt ( ID NUMBER, NAME VARCHAR2(30) ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY backup LOCATION ('a.dmp') it return the following error ORA-29913: error in execu

  • How to find the count of tables going for fts(full table scan in oracle 10g Update:11-30

    HI how to find the count of tables going for fts(full table scan) in oracle 10g regardsHi, Why do you want to 'find' those tables? Do you want to 'avoid FTS' on those tables? You provide little information here. (Perhaps you just migrated from 9i and

  • Is there any method to export the data from oracle 10g in any format !!!!! Update:11-30

    is there any methods to export the data from oracle 10g in the format of excel or csc or txt or anyother. i already downloaded the sql developer tool but it only exports the data of upto one lakh rows ,but i have two tables of more than 3 lakh and 10

  • Problem in importing data in oracle 10g from 9i backup Update:10-11

    Hi , Am trying to import data in oracle 10g..but it does not importing constraints, and stop doing anything by the msg. About to enable constraints..... after this msg it does not work. plz help, Regards, NehaHi Neha, You have a lot of options. I'll

  • How to delete the recyclebin data  in oracle 10g Update:11-30

    i create one table.then i drop the that table.this table will be kept in recyclebin in oracle10g .how to remove table permanentley in oracle 10g. i used drop table table name and select * from tab;it will come two tables like BIN$kLM/ilgxTUil64ZsNG0l

  • Migration using data pump Oracle 10g - Oracle 11g Update:11-30

    HI all, 1) Right now I`m using Oracle 11g. I have a plan to import data from Oracle 10g. I would like to know if its possible to import data which was exported by datapump on Oracle 10g ? Should I convert somehow expdp output from Oracle 10g to Oracl