Creating Projections on a table

[rajasekhart]

Hi,

I want to create a projection on a table.
we already knew that when a table is created, obviously a super projection will be created to that table.
But i want to create a projection on a table other than a super projection. As we know that one can create a max number of 50 Projections on a table.

But my doubt is , how to use those encoding techniques (RLE,DELTAVAL,BLOCK_DICT,COMMONDELTA_COMP,Lempel Ziv) while creating projections?

And once a projection(other than a super projection) created on a table, does the speed of retrieving(by querying that projection) data increases or not?

Please help me

Thanks,
Raj

[nnani]

Raj,

You need to be well versed with the Projections.

Whenever you insert a single data into your empty table the Super projections are automatically created.
When using DBD tool you can create other projections for the same table.

Coming to your doubt, The encoding type depends on the Data type of the column. Most of the times the DBD script will help you with the appropriate encoding, but sometimes manually scripting the encoding types is more beneficial.

The other doubt: It all depends on design of your new projections, If the sort order, segmentation, cardinality, other factors are kept in mind while creating the new projection, then your queries can get benefited garunteed.

[rajasekhart]

Hi,


Please show me an example of file (having script of query specific projections) that you r submitting to run in admintools.

Thanks,
Raj

[nnani]

Raj,

Here is a simple example of a file which can be used to provide to the DBD for query specific design.
Create a file by .sql extension.
copy all your queries in the file.
SELECT * FROM DW_T.look_In_Hist WHERE time=1334549001;
SELECT * FROM DW_T.look_In_Hist WHERE time>=1334549001 AND time<=1384848089;
SELECT * FROM DW_T.look_In_Hist WHERE proj_cd='K53';
SELECT * FROM DW_T.look_In_Hist WHERE mid='PJ4010000';
save the file.

Just put the correct path of the file when the DBD prompts you for the path.

You can check the design of the new projections suggested by the DBD which comes in <name>_deploy.sql format.

Note - With Version 5.1 you can use a limit of 100 queries in a file for the DBD at a time.

[Rick]

You can do this through database designer, by selecting the appropriate optimization.

Or if you want further customized projections you can just use their pre-made projection files to help you make new ones then just run the sql scripts. For example:

Code: Select all

CREATE PROJECTION public.customer_dimension_my_projection
(
 customer_key ENCODING DELTAVAL,
 customer_type,
 customer_name,
 customer_gender ENCODING RLE,
 title,
 household_id ENCODING DELTAVAL,
 customer_address,
 customer_city,
 customer_state,
 customer_region,
 marital_status,
 customer_age ENCODING DELTAVAL,
 number_of_children ENCODING DELTAVAL,
 annual_income ENCODING COMMONDELTA_COMP,
 occupation,
 largest_bill_amount ENCODING DELTAVAL,
 store_membership_card ENCODING DELTAVAL,
 customer_since ENCODING DELTAVAL,

Then edit it as you like and rename the script and run it using \i.