Create A Function Based Index On Bold Highlighted
Jul 2, 2010
Using oracle 10g R2 on sun-solaris 10 (sparc-64) Well in the MIS system we have lot of ad-hoc queries coming up. We have proper indexing. Say an example which runs very slow;
SELECT GLKCO, GLDCT, GLDOC, GLDGJ, GLJELN, GLEXTL, GLPOST, GLICU, GLICUT, GLDICJ, GLDSYJ,
GLTICU, GLCO, GLANI, GLAM, GLAID, GLMCU, GLOBJ, GLSUB, GLSBL, GLSBLT, GLLT, GLPN, GLCTRY, GLFY, GLFQ,GLCRCD, GLCRR, GLHCRR, GLHDGJ, GLAA, GLU, GLUM, GLGLC, GLRE, GLEXA, GLEXR, GLUPMJ, GLUPMT, GLBCRC, GLCRRM, GLACR, GLAN8
FROM "PRODDTA"."F0911"
WHERE GLUPMJ <> 0
AND TO_DATE('1 JAN' || (19+substr( GLUPMJ , 1, 1)) || substr( GLUPMJ ,2,2)) + substr( GLUPMJ , 4, 3 ) -1 BETWEEN SYSDATE - 365 AND SYSDATE
[code]....
Here GLUPMJ already indexed so the second query returing an index scan but the first query does a FTS naturally.Now even if I plan to create a function based index on 'the bold highlighted' but how.
create index glupmj_idx on f0911(TO_DATE('1 JAN' || (19+substr( GLUPMJ , 1, 1)) || substr( GLUPMJ ,2,2)) + substr( GLUPMJ , 4, 3 ));..Error If I don't use a FBI my query will result in FTS.
1> how to create a FBI here in this case
2> In MIS systems where 'n' no of ad-hoc queries can come up, how to avoid FTS.
View 4 Replies
ADVERTISEMENT
Jun 15, 2012
Is anyway to create function based index for group function columns.
For example
select max(timestamp),min(age),averge(sal).... ... .. from tab;
View 5 Replies
View Related
Nov 22, 2010
I need to trap user input by checking if the node in my tree is selected (higlighted) or not. When I test using SYSTEM.TRIGGER_NODE_SELECTED, whether I click the node to select (the node is highlighted) OR I click the plus (+) sign to expand the node, the function both retunrs TRUE.
I don't the user to add node when the current node is not selected and highlighted.
View 1 Replies
View Related
Jun 27, 2011
I have a table which sees a lot of use for queries
CREATE TABLE CASE_STAGE
(
ID NUMBER(9) NOT NULL,
STAGE_ID NUMBER(9) NOT NULL,
CASE_PHASE_ID NUMBER(9) NOT NULL,
"CURRENT" NUMBER(1) NOT NULL,
--and other columns
)
ID is a primary key
CASE_PHASE_ID is a foreign key
"CURRENT" should only ever have values of 0 or 1. When it has a value of 1 it is unique for that CASE_PHASE_ID
What I have tried that doesn't work is
create unique index case_stage_F_IDX1 on case_stage("CURRENT", case_Phase_id) which gives me
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
What is the correct syntax, something like ("CURRENT"=1,case_phase_id) seems right but fails with an error about a missing bracket. Do I need to use a CASE statement here?
View 1 Replies
View Related
Jun 22, 2011
I have created a function based index(FBI) with trim(header_date), but when i query the table by passing the hardcoded date, it is not working and i have to manually apply trim to get the result?
my query after applying FBI is
select * from abc where header_date = '21-JUN-11', no results are returned and when i apply trim to header_date it works fine .
View 4 Replies
View Related
Feb 22, 2012
I have a table which has 4M records
This table has a query where one of the condition is
AND STATUS <> 'C'
Now the data is as following
select count(*) record_count, status from new_business group by status;
record_countstatus
4298025C
15N
13Q
122S
I want to know if following index would be useful in this case while the condition in where clause is
"AND STATUS <> 'C'"
create index nb_index_1 on new_business(case when status in('N','Q','S') then 1 else NULL end);
Or
create index nb_index_1 on new_business(case when status ='N' then 'N' when status='Q' then 'Q' when status='S' then 'S' else NULL end);
I tried it on a sample table but the index is simply not picked up even when hinted following are the db level settings
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced
I tried it 'query_rewrite_integrity' with 'trusted' as well .
View 7 Replies
View Related
Apr 3, 2012
One of our query is not using function based index, the required priv is granted to the user executing the query and also tables stats are gathered? what could be the reason for the query to not to pick the FBIndx? the table is a huge one having million of records, is it that CBO thinks that not picking FB indx is the best execution plan? let me know how can we make the query use the FB indx, also there is a restriction that we cannot force it using hints.
View 3 Replies
View Related
Jun 27, 2013
It is not possible to run SHRINK SPACE against a table with a function based index. This is documented, and I've tested on the current release. I've reverse engineered it a bit, and the issue is in fact that you cannot SHRINK SPACE if there is an index on a virtual column:SQL> SQL> create table t1(c1 number, c2 as (c1*2)) segment creation immediate;
Table created.
SQL> alter table t1 enable row movement;
Table altered.
SQL> alter table t1 shrink space;
Table altered.
SQL> create index i1 on t1(c2);
Index created.
SQL> alter table t1 shrink space;
alter table t1 shrink space
ERROR at line 1: ORA-10631: SHRINK clause should not be specified for this object.
View 2 Replies
View Related
Nov 17, 2010
I'm trying to create bold and normal text in the same paragraph with the library PL/PDF, but I can't. This is a small example:, this is an example to the library PL/SQL, adding text in bold and normal text." This text should be justified on the margins I can set.
View 3 Replies
View Related
Jul 11, 2012
Can we create non-cluster index on a clustered index?
View 5 Replies
View Related
Mar 29, 2012
I have noticed a very questionable phrase on an article updated in 2011: "Oracle SE may allow you to create a function-based index, but you must pay for an EE license to use FBI's." [URL] Is this true? I have tested a FBI on my SE and works just fine.
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL> create table t ( col varchar2(10));
Table created.
SQL> create index t_idx on t(upper(col));
Index created.
SQL> insert into t values('a');
[code]....
View 11 Replies
View Related
Jun 26, 2012
I have the below SQL with cust_id is indexed
DELETE FROM ALERT WHERE TRIM(CUST_CD)=TRIM('100350378');
Output of the plan table is
OBJECT_NAME OPTION COST
ALERT 1866
ALERT BY INDEX ROWID 1866
DELETE FROM ALERT WHERE CUST_CD=TRIM('100350378');
Output of the plan table is
OBJECT_NAME OPTION COST
ALERT 3
ALERT BY INDEX ROWID 3
how to use trim on indexed column.
View 1 Replies
View Related
Apr 14, 2012
I have a requirement where the user input values will be passed as comma separated string to backend, which is queried against the table using instr. But the index present on the table is not using the index , due to the instr function.How can I create a index in such a way that The instr function uses it.
CREATE TABLE TEST_IDX
(
CCN VARCHAR2(10 CHAR),
SKU_NUM VARCHAR2(10 CHAR),
MOD VARCHAR2(10 CHAR),
SKU_STATUS VARCHAR2(2 CHAR),
RPT_FLAG VARCHAR2(2 CHAR),
CYCLE_AS_OF_DATE DATE,
SMP_IDENTIFIER NUMBER,
MEASURE_NAME VARCHAR2(100 CHAR)
);
CREATE INDEX TEST_IDX1 ON TEST_IDX
(CCN, SMP_IDENTIFIER, MOD, CYCLE_AS_OF_DATE, RPT_FLAG,
MEASURE_NAME);
The below query is going for full table scan due to this.
select * from test_idx where (INSTR (','||'E10000'||',', ',' || ccn || ',') <> 0 OR 'E10000' = 'DEFAULT')
and mod='90396' and rpt_flag='O' and smp_identifier=2
how to recreate the above index so that these queries uses this index.
View 16 Replies
View Related
Nov 4, 2010
I have the following query with analytic function but wrong results on the last column COUNT.
1)I am getting the output order by b.sequence_no column . This is a must.
2)COUNT Column :
I don't want the total count based on thor column hence there is no point in grouping by that column. The actual requirement to achieve COUNT is:
2a -If in the next row, if either the THOR and LOC combination changes to a new value, then COUNT=1
(In other words, if it is different from the following row)
2b-If the values of THOR and LOC repeats in the following row, then the count should be the total of all those same value rows until the rows become different.
(In this case 2b-WHERE THE ROWS ARE SAME- also I only want to show these same rows only once. This is shown in the "MY REQUIRED OUTPUT) .
My present query:
select r.name REGION ,
p.name PT,
do.name DELOFF,
ro.name ROUTE,
[code]...
My incorrect output[PART OF DATA]:Quote:
REGIONPT DELOFF ROUTE THOR LOC SEQ COUNT
NAASNAAS MAYNOOTHMAYNOOTHR010 DUBLINRD CEL 1 1
NAASNAAS MAYNOOTHMAYNOOTHR010 NEWTOWNRD CEL 2 1
[code]...
My required output[PART OF DATA]-:Quote:
REGIONPT DELOFF ROUTE THOR LOC COUNT
NAASNAAS MAYNOOTHMAYNOOTHR010 DUBLINRD CEL 1
NAASNAAS MAYNOOTHMAYNOOTHR010 NEWTOWNRD CEL 1
NAASNAAS MAYNOOTHMAYNOOTHR010 PRIMHILL CEL 1
[code]...
NOTE :Count as 1 is correctly coming.But where there is same rows and I want to take the total count on them, I am not getting.
View 9 Replies
View Related
Oct 24, 2013
Oracle 11g databaseidval1val2100a110b120c200a220b
WITH input AS
(SELECT 1 id
[Code].....
input; Output:idval1val2assigned_number100a0110b0120c2200a0220b1 The dense numbering sequence should be assigned to each row based on id and val1 column. For a given Id, the numbering only starts after val1 > 1 till then the assigned_number will be zero.
View 1 Replies
View Related
Apr 30, 2013
I work with Oracle Forms and Report Builder since several weeks, but now i have something that i want to do with Report Builder.
I get string from DB (It's a paragraph) But i would like to set bold weight on a part of the string
for now, i set bold text i want to and i have created a fonction that make text bold like this
l_chaine := (SUBSTR(MyStrFromDB, InSTR(UPPER(MyStrFromDB), '[B]'),InSTR(UPPER(MyStrFromDB), '[/B]') ));
if(length(l_chaine) > 0) then
SRW.SET_FONT_WEIGHT(SRW.BOLD_WEIGHT);
end if;
but it doesnt look to works..
how can i do that? And where i can do that?
View 2 Replies
View Related
Jan 28, 2013
from those who have created complex reports/forms using PL/PDF. done strike through sentences and words in bold within paragraphs?
View 0 Replies
View Related
Jul 31, 2013
I want to create an XML based on xsd and then i want to validate the same xml with xsd ,
View 1 Replies
View Related
Jun 26, 2012
CREATE TABLE DAN_DATES
(ID VARCHAR2(12),
YEAR VARCHAR2(,
TERM VARCHAR2(,
START_DATE VARCHAR2(12))
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('1','2012','1201',to_date('20120227','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('1','2012','1201',to_date('20120626','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2011','1101',to_date('20110226','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2011','1101',to_date('20110725','YYYYMMDD'));
INSERT INTO DAN_DATES (ID,YEAR,TERM,START_DATE) VALUES ('2','2012','1201',to_date('20120227','YYYYMMDD'));
Want to take the Start_Date for that year, CREATE A NEW COLUMN and place that START_DATE (which is row 1 for the year (min)) in it. So for ID 1 TERM is 1201 and 1202 BUT we want the top start date (earliest start date) and CREATE a clumn (NEW_START_DATE) and place that date in there wherever year is 2012.
I want to get
IDYEARTERMSTART_DATEMIN_DATE
12012120127-Feb-1227-Feb-12
12012120126-Jun-1227-Feb-12
22011110126-Feb-1126-Feb-11
22011110125-Jul-1126-Feb-11
22011110126-Sep-1126-Feb-11
22012120227-Feb-1227-Feb-12
View 10 Replies
View Related
Jan 27, 2013
i want to create a bitmap index,but getting the error shown below..i created primary key on column dname of dept and unique constraint on empno of emp.
SQL> create bitmap index bindx on dept d(d.dname) from emp e,dept d where e.deptno=d.deptno;
create bitmap index bindx on dept d(d.dname) from emp e,dept d where e.deptno=d.deptno
*
ERROR at line 1:
ORA-25954: missing primary key or unique constraint on dimension
View 1 Replies
View Related
Nov 11, 2010
I want to Bold the value with highest value in Numeric field. how can i do this.
View 12 Replies
View Related
Oct 10, 2012
CREATE TABLE test1
(strt_num NUMBER ,
end_num NUMBER ,
des VARCHAR2(5),
CONSTRAINT pk_strt_num PRIMARY KEY (strt_num)
);
INSERT INTO test1 VALUES (5, 8, 'GC');
INSERT INTO test1 VALUES (10, 25, 'AB');
INSERT INTO test1 VALUES (12, 35, 'PC');
INSERT INTO test1 VALUES (22, 65, 'LJ');
SELECT * FROM test1
STRT_NUM END_NUM DES
-------------- ------------ -------------
5 8 GC
10 25 AB
12 35 PC
22 65 LJ
The requirement is the records should be split based on below conditions
1. Split only those records WHERE (end_num - strt_num) > 10
2. If TRUNC((end_num - strt_num)/10) = n, then n + 1 number of rows should be created for that record
3. While splitting the records,
-> For first record , START_NUM = Original STRT_NUM and END_NUM = START_NUM + 10
-> Second record , STRT_NUM = previous END_NUM + 1 and END_NUM = previous END_NUM + 10
And this should continue for all records except the last record
-> For last record, STRT_NUM = previous END_NUM + 1 AND END_NUM = Original END_NUM
This table has 5 million records. Only for 2000 records (end_num - strt_num) > 10.
Expected Output.
STRT_NUM END_NUM DES
------------- -------- ---------
5 8 GC -- No chage, END_NUM - STRT_NUM < 10
10 20 AB
21 25 AB
12 22 PC
23 32 PC
33 35 PC
22 32 LJ -- STRT_NUM = Original STRT_NUm, END_NUM = STRT_NUM + 10
33 42 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
43 52 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
53 62 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
63 65 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = Original END_NUM
View 7 Replies
View Related
Aug 6, 2010
How to create index on materialized view which is refreshed daily basis.
View 3 Replies
View Related
Feb 25, 2013
difference between the ways we create Reverse Key Index on the table.The two ways are as below:
1) CREATE INDEX <Index_Name> ON <Table_Name>(<Column_Name) REVERSE ;
2) CREATE INDEX <Index_Name> ON<Table_Name>(REVERSE(<Column_Name>)) ;
Which one is the appropraite one.
Are both going to act in the same way.
I created index in both ways one by one on the table.But when I fire the select statement against the same table, The explain plan doesn't show any cost and it shows the full table scan in both ways.The select statement used is as below...I want to compare the column with string RBO(i.e('RBO%') at the start.
SELECT *
FROM <Table_Name>
WHERE REVERSE (<Column_Name>) LIKE '%OBR' ;
The select statement is giving me the correct result. Only the explain plan is showing Full Table Scan.can we use this reverse with IN operator also in the same way by reversing the values.
View 3 Replies
View Related
Apr 7, 2010
I use Oracle 10.0.2.0.1.If I create a table with constraint key; after that I create an unique index key, I got an error. Does it mean when I create a table with constraint the unique index are automatically created and I could not create index key as I did as below?
create table test_const(ename varchar2(50) not null,
key_num number not null,
descr varchar2(100),
constraint constraint_test_const unique (ename, key_num));
create unique index test_const_idx on test_const
(
"ENAME","KEY_NUM"
)
tablespace tmp_data;
Error report: SQL Error: ORA-01408: such column list already indexed 01408. 00000 - "such column list already indexed" added [pre] tags by Sriram
View 3 Replies
View Related
Mar 30, 2013
I am going through this scenario:
* 35 | ID TABLE ACCESS BY INDEX ROW | S_ORG_EXT | 3064K| 2472M| | 1 (0)| 00:00:01 |
| 36 | INDEX FULL SCAN | S_ORG_EXT_U1 | 14 | | | 1 (0)| 00:00:01 |
Predicate Information (identified by operation id):
---------------------------------------------------
35 - filter("T2"."ACCNT_FLG"<>'N' AND ("T2"."INT_ORG_FLG"<>'Y' OR "T2"."PRTNR_FLG"<>'N'))
This unselective index scan on step 36 of the explain is returning 14 rows but optimizer is selecting 3064 K rows from the table .
I tried creating combined index on all 3 columns mentioned in the predicates for 35th step , but that is not utilized .
how to index this whole expression ::--
(ACCNT_FLG<>'N' AND (INT_ORG_FLG<>'Y' OR PRTNR_FLG<>'N'))
Something like CREATE INDEX XYZ on table((ACCNT_FLG<>'N' AND (INT_ORG_FLG<>'Y' OR PRTNR_FLG<>'N')) compute statistics ;
View 3 Replies
View Related
Jul 12, 2012
how to create a primary key with out creating an index?
View 10 Replies
View Related
May 21, 2013
we create index on multiple tables in oracle
View 12 Replies
View Related
Nov 10, 2010
Is it a possible to create table using clause below together with index ?
create table the_table
as
select col1, col2 from table2
I got procedure which create a table in the schema B. The procedure is called from schema A. But when I write into procedure query for create index
then I got a error:
ORA-01031: insufficient privileges when
...executing
Therefore I think about to create table together with index.
begin
B.proc.cre_table;
end;
View 6 Replies
View Related
Dec 3, 2012
I have an IOT table with partitioned on list. I have p1,p2 and p3 partitions. Now I would like to create a bitmap index on partition key.
I gave ALTER TABLE .. MOVE MAPPING TABLE;
But getting the below error,
ORA-28660: Partitioned Index-Organized table may not be Moved as a whole.
View 1 Replies
View Related
