OOW Notes - Some new features in SQL Developer 4.0

cursor on * brings all columns

select * from t_asset_brand;


explain plan button

explain plan brings drop down for the executed plans if the sql is executed


connection properties

connection color in the connection properties to change the border colors of the inner windows


cart option

menu -> view -> cart

drag and drop the tables to the cart window
create scripts for both ddl and dml
diff reports between carts
export cart data and ddl


advanced chart reports

you can test it before saving
while developing you can see the changes immediately


pdf exports are enhanced

allowed to generate template
allowed to add header and footer
create a detail report and generate into a secure pdf
report properties->pdf->security in order to add password protection to the pdf exports

OOW Notes - High Volume Text Indexing

Tuesday 12:00 - HighVolume text indexing with Oracle Text - Roger Ford

create index <index_name> on <table_name> (column_name) index type is ctxsys.context;


select from table_name
where
contains (product_name, 'monitor NEAR full hd', 99) > 0
order by score(99) desc;


index types:

• context (primary)
  
• ctxcat
  
• ctxrule
  
• Xquery Full Text
  

context index is inverted
consists list of words each with an associated posting list
posting lists can be very long (requires IO)
are stored in $I table
SGA works as is on this object always pin this table to SAG or flash cache if possible


Common Pitfalls


Memory

• when indexing, postings lists are built in memory then flushed to disk when memory is exhausted
  
• frequent flushes = small fragments
  

in memory controlled by MEMORY parameter


create index index_name
on table_name(col)
index type ctxsys.context parameters ('memory 1G');


exec ctxsys.ctx_adm.set_parameter('MAX_INDEX_MEMORY','10G');


Substring index / UTF8


context indexes are token (word) based


using leading wildcards are slow


need to turn on SUBSTRING_INDEX wordlist option


increases index build time

1. : 5x long
   
2. : %20 long
   

AL32UTF8 has significant indexing overhead (consider single byte character set)


Frequent syncs

• context indexes are asynchronous
  
• sync(on commit) will cause index fragmentation
  
• can be cured by ctx_ddl.optimize_index
  

transactional will require unindexed documents to be scanned by all queries
use transactional only with sync(on commit)


if syncing frequently, optimize frequently
use 12c feature (near real time indexes)

Big IO

• previously $I index were stored inline BLOBs
• new storage attribute BIG_IO uses secure file lobs

Seperate offsets

posting lists consists of document Ids and word position in the document

some queries does not need word position

new separate_offset storage attribute will hugely reduce IO for these queries

will make proximity (NEAR), phase and zone section searcher will be a bit slower

Near Real Time Indexes

new storage attribute STAGE_ITAB

• creates two level index
• small enough to keep in memory
• large main index stored in the disk
• updates are moved from staging index to main index and optimized as they move
• requires BIG_IO storage parameter in order to use

Query Filter Cache

some expressions are common to many queries

security restriction: .. “(sales or marketing) WITHIN dept”

postings entries for these queries may be very large

postings will likely be cached in sga but still need to be decompressed and merges

query filter cache, caches the results of subquery in memory

query operator ctxFilterCache(subquery)

Pattern stop class

specify regular expression exclusion list for index

large indexes can be %75 junk

• reference codes
• sequential/numeric data
• wrongly indexed binary data

example:

• exclude all words longer than 20 chars
• exclude all words which are mixed alpha and numeric

Partitioning

• partitioned index can be created on partitioned base tables
• use local keyword for indexes
• currently only range partitioning is supported
• partitioned indexes looks like separate indexes on partitions

• better query performance on subset
• less interference between indexing and querying
• caching of more common target positions

Parallelization

parallel index creation is supported by using parallel n clause

can mix local and parallel but max 1 process per partition (db restriction)

to do intra-partition parallelization

• create index using unusable keyword
• run dbms_pclxutil.build_part_index and specify degrees on parallelization

index is unavailable until complete

no option for incremental builds

manual partition builds

create index using nopopulate parameter

build individual partitions using

alter index index_name rebuild partition partn parallel n;

allows complete control over parallelization

index is usable as soon as one operation is built

unlike unusable option; can build incremental changes by loading new partition and rebuilding

no sync required for non changing data

faster use partition swapping

distributed index builds

• indexes can be built on farm of machines
• partitioned base table on master with local indexes
• non partitioned table on remote instance
• build index on remote table, then transfer to master using transportable tablespace

swap in index using exchange partitioning

alter table table_name

exchange partition main_table_p10

with table remote_table

including indexes

without validation;

• almost instant ($K IOT must be copied)
  
• new data immediately searchable
  

OOW Notes - What is new in Oracle DB App. Development.

Monday 12:15 - Whats new in Oracle Database App. Development – Thomas Kyte


Database improvements


Application client continuity called TAF


problem: if you click an application button twice, did it really committed twice or if the node failure happened and my transaction rolled back (or finished successfully)


Transaction guard – first rdbms to preserve commit outcome.. at most one transaction execution. Used by jdbc-thin, oci, occi, odp.net


now able to ask if the transaction is rollback or commited to the transaction guard.


Logs are info held in the client side application server.


Sql translation framework


by creating profiles now it is able to transform transact-sql to oracle pl-sql.

--create a profile and translation
dbms_sql_translator.create_profile(profile_name);
dbms_sql_translator.register_sql_translator(profile_name, 'select * from scott.emp','select* from scott.dept');

--in order to use in a transaction
alter session set sql_translation_profile=profile_name;
alter session set events '10601 trace name context forever, level 32';


APEX improvements

michael fitcher → web db → html db → apex

web browser → web listener / or web server with mod_plsql / or apex listener → database

4.2.3 is available

html5 supported charts and page items

packed applications

• ask us (asktom.oracle.com)
• group calendar
• project tracking
• issue tracker
• checklist manager
• meeting minutes

full support for apex in oracle multi-tenant architecture (apex installed in a container, patched and upgraded across all pluggable databases)

full support for 32K varchar2 columns

integration with real application security coming in apex 5.0

http://apex.oracle.com/oow

SQL Developer technology

sql command line extended reporting and charting.

Cart is enhanced.

Reports are greatly enhanced..

UI enhancements → border colors now able to change on connections

includes data modeler version 4.0

jdk 7 support and above

10012 → what is new in sqldeveloper 4.0 (I am already registered)

ODP.NET

transaction guard support guarantees that a commit should be done at most once.

Support for PLSQL type (boolean)

Support for all 12c types.

Multitenant support

Impcilicit ref cursor support

Array binding

Apply keyword in SQL for enhanced LINQ support

global data services support

• region based workload routing
• connect time load balancing → global data services (gds)
• distributed replicas

Networking

Data compression levels

• connection level (connection string)
• service level (tnsnames.ora)
• database level (sqlnet.ora)

Large buffers

• default sdu is 8k (was 2k before 11g)
• max values was 64k
• max values is now 2m (clobs and blobs)

Multitenant data source

DRCP

Database resident connection pool vs shared server (serious differences and advantages)

JDBC

• Jpublisher maps packages to jdbc in 12.1
• In database map reduce !?!
• Drop in hadoop mappers and reducers (run as is)
• Support for hadoop ecosystem components
• SQL support for hadoop

OCI

Performance

• xml based client config file
• auto tune of statement cache size
• array dml returns affected row count in an array

Database

• full pluggable database support

SQL and Datatypes

• long string support
• row count byte is 8byte integer from now on
• bind directly to plsql packages
• implicit result set supported

PL/SQL

query plsql table type directly (no more to create sql types)

less namespace clutter

grant roles to code

• invokers rights routines
• code will run with current set of privileges of the invoker plus that roles
• white list (that sp can only be runned from other piece code but never from sqldeveloper)
• fine grained privilege to protect code

improved introspection

utl_call_stack → provides procedural, structured access to the information

references:

www.mhprofessional.com/dbsec