I have the following situation. There are two selects in the which look like this
UPDATE TABLE_B B
SET (
target
[Code]....
DB: Oracle 10i
MyFunction is relatively expensive and the second select works on a subset of the data considered in the first one (because of a.col2 > b.col3). For this reason I am looking for a way to do the job in only one update statement and compute MyFunction (a.x) only once per row.
Correct example
select
pp.IVA
, pp.CNR
, pp.POLO
,(pp.CNR-pp.POLO) diff
, ((pp.CNR-pp.POLO) * pp.IVA / 100) + (pp.CNR-pp.POLO) diva
from AB_PERIODI_PREZZI pp
Invalid example
select
pp.IVA
, pp.CNR
, pp.POLO
,(pp.CNR-pp.POLO) diff
, ((diff * pp.IVA / 100) + diff) diva
from AB_PERIODI_PREZZI pp
In my real view, the diff calculate column is very long (with case etc...) I must reuse many time the diff calculate column to calculate other columns.I do not want use subquery because there is many join to do in the same query. How can I reuse a calculate column in the same view?
Is there a way to define a SELECT clause once and reuse it in many other queries?
I have many procedures with same SELECT statement and I'm trying to find a way to not have to write out the SELECT clause in every function or procedure. Same question applies to FROM, WHERE, etc clauses.
I would like to reuse the icon button template for my button in a tabular form.
I tried to add <button type="button" class="uButton iconButton search"> Click </button> in column link text. I display this column like a Standard Report Column. The result it's not good.
How I can have the icon and button template like in my others buttons ?
My data warehouse application involves partitioned tables where indexes are originally unusable on the last partition and only built until the next partition is created. We have a query tool that our users use to query this table that has an option "include not indexed data", which is essentially telling the tool whether to include that last partition in the query. IF this is checked, and they are filtering against on of the indexed fields, there is the potential for an Oracle error stating it tried to use an unusable index so our tool basically builds the query like this:
select ... from (
select ... from table where partition_key < (last usable partition key)
union
select /*+ NO_INDEX */ ... from table where partition_key >= (last usable partition key)
)
where
index_field = :value
I have had a difficult time getting reasonable data to test this myself, so I'm asking the question here:
Is Oracle probably pushing that outer filter into the inner individual queries in the UNION? If we were to move the index_field filter into the inner query against each of the individual queries in the union, would it make a difference performance-wise?
I am looking for some tools for Performance analysis and optimization for Oracle. For now I looked over Spotlight, Ignite and Embarcadero DB Optimizer.
View 1 Replies View RelatedI have long select which operate on 5 tables and has a lot of conditions in where clause (many combinations of values of just a few columns). Does reducing of those conditions could improve performance or just has a small impact?
I think if I have a lot of conditions on the same column, it don't take a lot of time to check them because values are in memory.
I have a Tablespace DP_TS_LOBS and i stores only secure file blobs in it. DP_TS_LOBS has only one datafile "LOB1.dbf" . I have RAMN optimization parameter on. I also have the backup of DP_TS_LOBS tablespace.
RMAN> backup tablespace dp_ts_lobs;
after storing few blobs the data file "LOB1.DBF" got full and added a new data file "LOB2.DBF". Few more BLOB's were stored to DP_TS_LOBS tablespace. Then I tried to backup the DP_TS_LOBS table space again, as expected both the data file were backed up as there were changes to both the datafile since last backup.
I was expecting with Optimization Parameter on, if RMAN has a datafile with same DBID, checkpoint SCN, creation SCN,RESETLOGS SCN,time already in the backup and data file is offline RMAN wont backup that data file again.
After few minutes with out performing any activities in the database, I put the "LOB1.DBF" to offline and executed the DP_TS_LOBS table space backup again but I still see RMAN backing up the both the data files.
I was closely monitoring the "CHECKPOINT_CHANGE#" and "CHECKPOINT_TIME" columns of v$datafile, the values for those columns changes for data files "LOB1.DBF" and "LOB2.DBF" when I execute backup tablespace command on DP_TS_LOBS even thought there was not any activity in the database to add or update or delete blob that are stored in DP_TS_LOBS table space.
Initially I thought it can be due to some unwritten committed blocks of dp_ts_lobs table space in the memory that got written to the datafiles while executing backup tablespace command and that why i saw the change in checkpoint scn and checkpoint time , but it keeps happening every time when I tried multiple times.
I have this table
create table ACTIONARI_ARH
(
actionar_id NUMBER(10) not null,
id VARCHAR2(20) not null,
id_2 VARCHAR2(20),
tip VARCHAR2(1),
nume VARCHAR2(100),
prenume VARCHAR2(100),
adresa VARCHAR2(200),
[code]....
and this view
CREATE OR REPLACE VIEW ACTIONARI AS
SELECT "ACTIONAR_ID","ID","ID_2","TIP","NUME","PRENUME","ADRESA","LOCALITATE","JUDET","TARA","CERT_DECES","DATA_REGISTRU" Data_operare,"USER_MODIF","DATA_MODIF","REZIDENT"
FROM (
select
[code]....
The table has about 30 milion records and holds persons names, addresses, personal id (id), and internal id(actionar_id) and date when a new adress has been added.
The view is about getting only the most recent info for one person (actionar_id).
if i run a
a) select * from actionari a where a.actionar_id = 'nnnnnnn', result is returned immediatly, oracle uses index and does not do a full table scan.
b) select * from actionari a where a.actionar_id in ('nnnnnnn','mmmmmm','ooooooo'), result is returned immediatly, oracle uses index and does not do a full table scan.
my problem when i use this view in a join.let's assume i have another table with no more than 500 records, something like
create table SMALL_TABLE
(
actionar_id NUMBER(10) not null,
......
);
and if i run
select *
from SMALL_TABLE s
join actionari a
on a.actionar_id = s.actionar_id;
it takes like forever to process, forever means 1~3 minutes.by looking at the execution plan, oracle does a full table scan, creates the view for all unique 7milion persons, and only then joins the result with the actionar_is's in the small table and returns the desired 500 record result.i am using oracle 10g.
I am studying about these two technologies and the only difference that I found was that optimization doesn't backup the duplicated archivedlogs.
If both ignore not changed blocks, what is more effective?
The optimization backup also has some tracking file?
I would like to know that if I enable backup Optimization on then incremental full backup skip any files which was earlier backup? Because we may know that backup Optimization on ensure skip those files which are already taken backup.
Database : oracle 10g 10.2.0.3 .