DBA Blogs

Excel Average Function – A Step By Step Tutorial

VitalSoftTech - Wed, 2019-11-13 10:33

Calculating average when you only have a few entries in your data is one thing but having to do the same with hundreds of data entries is a whole another story. Even using a calculator for finding the average of this many numbers can be highly time-consuming and to be honest, quite frustrating. After all, […]

The post Excel Average Function – A Step By Step Tutorial appeared first on VitalSoftTech.

Categories: DBA Blogs

Basic Replication -- 11 : Indexes on a Materialized View

Hemant K Chitale - Tue, 2019-11-12 08:46
A Materialized View is actually also a physical Table (by the same name) that is created and maintained to store the rows that the MV query is supposed to present.

Since it is also a Table, you can build custom Indexes on it.

Here, my Source Table has an Index on OBJECT_ID :

SQL> create table source_table_1
2 as select object_id, owner, object_name
3 from dba_objects
4 where object_id is not null
5 /

Table created.

SQL> alter table source_table_1
2 add constraint source_table_1_pk
3 primary key (object_id)
4 /

Table altered.

SQL> create materialized view log on source_table_1;

Materialized view log created.

SQL>


I then build Materialized View with  an additional Index on it :

SQL> create materialized view mv_1
2 refresh fast on demand
3 as select object_id as obj_id, owner as obj_owner, object_name as obj_name
4 from source_table_1
5 /

Materialized view created.

SQL> create index mv_1_ndx_on_owner
2 on mv_1 (obj_owner)
3 /

Index created.

SQL>


Let's see if this Index is usable.

SQL> exec  dbms_stats.gather_table_stats('','MV_1');

PL/SQL procedure successfully completed.

SQL> explain plan for
2 select obj_owner, count(*)
3 from mv_1
4 where obj_owner like 'H%'
5 group by obj_owner
6 /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2523122927

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 10 | 15 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT| | 2 | 10 | 15 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MV_1_NDX_ON_OWNER | 5943 | 29715 | 15 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

2 - access("OBJ_OWNER" LIKE 'H%')
filter("OBJ_OWNER" LIKE 'H%')



Note how this Materialized View has a column called "OBJ_OWNER"  (while the Source Table column is called "OWNER") and the Index ("MV_1_NDX_ON_OWNER") on this column is used.


You  would have also noted that you can run DBMS_STATS.GATHER_TABLE_STATS on a Materialized View and it's Indexes.

However, it is NOT a good idea to define your own Unique Indexes (including Primary Key) on a Materialized View.  During the course of a Refresh, the MV may not be consistent and the Unique constraint may be violated.   See Oracle Support Document # 67424.1



Categories: DBA Blogs

Joining two tables with time range

Tom Kyte - Mon, 2019-11-11 11:49
Dear AskTom-Team! I wonder whether it is possible to join two tables that have time ranges. E.g a table 'firmname' holds the name of a firm with two columns from_year and to_year that define the years the name is valid. Table 'address' holds the a...
Categories: DBA Blogs

TDE Encryption of local Oracle databases. KEK hosted on cloud service?

Tom Kyte - Mon, 2019-11-11 11:49
Hi, We want to encrypt some on-premise Oracle databases. If possible, we would like to avoid to use a physical HSM or to contract with a third party HSM cloud provider. Is this possible to store the KEK's in GCP or Azure, and to interface our lo...
Categories: DBA Blogs

Migration from oracle 6i and 11g to Oracle APEX

Tom Kyte - Mon, 2019-11-11 11:49
Dear all, i have oracle forms that are built on 6i and 11g and i want to migrate them to oracle apex, is it possible to create an application based on the oracle 6i form migrated, if so can you provide me with a document or a video showing the pro...
Categories: DBA Blogs

One way Encryption where no one can decrypt

Tom Kyte - Mon, 2019-11-11 11:49
Hi Tom, Kindly suggest some one way encryption for the data in table,where no one can decrypt it? Read few articles about this and not satisfied. Kindly help.
Categories: DBA Blogs

Connection retry when database services failover

Tom Kyte - Mon, 2019-11-11 11:49
Hello, Ask Tom, Team. I have a two-node RAC running a database service with one preferred instance and one available instance. It is supposed that RAC automatically failovers the services to available when preferred instance crashes. 1. Will ...
Categories: DBA Blogs

Best Practice using database services

Tom Kyte - Mon, 2019-11-11 11:49
Hello, Ask Tom Team. We have a two-node RAC running a database. The app connects to the database using scan name (best practice), so we are not using vip to connect to database directly. The tnsnames.ora is: DBPROD = (DESCRIPTION = (ADD...
Categories: DBA Blogs

Impact of Index Monitoring in production server

Tom Kyte - Mon, 2019-11-11 11:49
Hi, we are planning to change global index to local index for partitioned table in production. Since we need to drop an index and create it again and we don't know the time it will take, we decided to monitor the index which are being used by the app...
Categories: DBA Blogs

Generating DDL in Oracle SQL Developer

Tom Kyte - Mon, 2019-11-11 11:49
Dear AskTom-Team! Is there a possibility in the Oracle Developer to suppress duplicate DDL code? For example when generating the DDL from my relational model the foreign key constraint is generated twice, i.e. for both tables that are involved in th...
Categories: DBA Blogs

Oracle Database Views and Tables for Oracle GoldenGate

DBASolved - Fri, 2019-11-08 16:40

Oracle GoldenGate for Oracle over the last few releases have been moving towards an integrated architecture.  This means that there is more views and tables within the Oracle Database that support Oracle GoldenGate.  You can quickly find these views and tables by using a where clause with a filter for GoldenGate: select * from all_views […]

The post Oracle Database Views and Tables for Oracle GoldenGate appeared first on DBASolved.

Categories: DBA Blogs

Clustered Indexes

Tom Kyte - Fri, 2019-11-08 02:48
Is clustered Index is faster than Non-clustered Index?
Categories: DBA Blogs

Platform list on whicb JAVA_JIT_ENABLED paramter can be enbaled

Tom Kyte - Fri, 2019-11-08 02:48
<b>Question:</b> As my application is using some JAVA function as UDF using <b>loadjava</b>. So, developers are suggesting to set <b>JAVA_JIT_ENABLED</b> parameter as <b>true</b>. I have read (https://docs.oracle.com/cd/B28359_01/server.111/b28320/in...
Categories: DBA Blogs

How can i extract data from a URL string in SQL?

Tom Kyte - Fri, 2019-11-08 02:48
Hi TOM, i am trying to extract some data from a URL and running into some issues. The URL's will look something like this: http://192.168.201.242:8000/express_get?time=$startDate&from=$phoneNumber&to=$CAMPAIGN&Id_agent=$idExternalUser&spent=$durat...
Categories: DBA Blogs

JAVA CLASSPATH Specificaiton

Tom Kyte - Fri, 2019-11-08 02:48
Hi, We have an Oracle 10g database running java on the database as java stored procedures. It is hosted on an IBM AIX frame. There are both 64bit and 32bit jvms installed. How do you specify the java classpath for java running in the database? W...
Categories: DBA Blogs

Cursors

Tom Kyte - Fri, 2019-11-08 02:48
Hi Tom, The Oracle users in the world should be thankful to you for all your help all the way .Many thanks for that. We are learning many things about Oracle that we could not learn by reading several Oracle books (other than your books) though ...
Categories: DBA Blogs

Alternate to pro*c batch programming

Tom Kyte - Fri, 2019-11-08 02:48
Dear Team, We would like to have suggestion on the topic related to alternate programming for the existing batch system in pro*c with oracle 12c database. Currently we have many number of batch jobs which are in pro*c (by interacting with oracl...
Categories: DBA Blogs

Create materialized view with ENABLE QUERY REWRITE for SDO_RELATE getting ORA-30373

Tom Kyte - Fri, 2019-11-08 02:48
I write a query like this: <code>CREATE MATERIALIZED VIEW MV REFRESH FORCE ON DEMAND start with (sysdate) next (sysdate+1/1440) ENABLE QUERY REWRITE AS SELECT O.ID DIREK_ID, MAX(LK.ADI) ISLETME_GERILIMI FROM xxx O, yyy AA, SY...
Categories: DBA Blogs

Copy row value down until next non-null row

Tom Kyte - Fri, 2019-11-08 02:48
Hi, Is there a simple way to achieve the following output in SQL? Wanted to populate the null values with the Country name until there is a new country name. Thanks with data as ( select 'Afghanistan Afghani' as country_currency, N...
Categories: DBA Blogs

Problem with very large varchar2 sorting in oracle 10g r2? Sorry, I can't verify my problem in LiveSQL because I need to run it in Oracle 10g r2

Tom Kyte - Fri, 2019-11-08 02:48
I am very sorry to waste your time with something that is probably VERY SIMPLE, but IT IS something I cannot understand ... I must be obfuscated <code>select level, lpad(to_char(level), 1000) from dual connect by level<=20 order by 2;</code> ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs