Tip: 看不到本站引用 Flickr 的图片? 下载 Firefox Access Flickr 插件 | AD: 订阅 DBA notes -- 
2008-06-26 Thu
In 10gR2, we introduced a new transformation, table elimination (alternately called "join elimination"), which removes redundant tables from a query. A table is redundant if its columns are only referenced to in join predicates, and it is guaranteed that those joins neither filter nor expand the resulting rows. There are several cases where Oracle will eliminate a redundant table. We will discuss each case in turn.
Primary Key-Foreign Key Table Elimination
Starting in 10gR2, the optimizer eliminates tables that are redundant due to primary key-foreign key constraints. Consider the following example tables:
create table jobs
(
job_id NUMBER PRIMARY KEY,
job_title VARCHAR2(35) NOT NULL,
min_salary NUMBER,
max_salary NUMBER
);
create table departments
(
department_id NUMBER PRIMARY KEY,
department_name VARCHAR2(50)
);
create table employees
(
employee_id NUMBER PRIMARY KEY,
employee_name VARCHAR2(50),
department_id NUMBER REFERENCES departments(department_id),
job_id NUMBER REFERENCES jobs(job_id)
);
and the query:
select e.employee_name
from employees e, departments d
where e.department_id = d.department_id;
In this query, the join to departments is redundant. The only column referenced in the query appears in the join predicate, and the primary key-foreign key constraint guarantees that there is at most one match in departments for each row in employees. Hence, the query is equivalent to:
select e.employee_name
from employees e
where e.department_id is not null;
The optimizer will generate this plan for the query:
-------------------------------------------
Id Operation Name
-------------------------------------------
0 SELECT STATEMENT
* 1 TABLE ACCESS FULL EMPLOYEES
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."DEPARTMENT_ID" IS NOT NULL)
Note that the IS NOT NULL predicate is not necessary if the column has a NOT NULL constraint on it.
Starting in 11gR1, the optimizer will also eliminate tables that are semi-joined or anti-joined. Consider the following query:
select e.employee_id, e.employee_name
from employees e
where not exists (select 1
from jobs j
where j.job_id = e.job_id);
Since employees.job_id is a foreign key to jobs.job_id, any non-null value in employees.job_id must have a match in jobs. So only employees with null values for employees.job_id will appear in the result. Hence, this query is equivalent to:
select e.employee_id, e.employee_name
from employees e
where job_id is null;
and the optimizer can choose this plan:
-------------------------------------------
Id Operation Name
-------------------------------------------
0 SELECT STATEMENT
* 1 TABLE ACCESS FULL EMPLOYEES
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("E"."JOB_ID" IS NULL)
Suppose employees.job_id has a NOT NULL constraint:
alter table employees modify job_id not null;
In this case, there could not possibly be any rows in EMPLOYEES, and the optimizer could choose this plan:
-------------------------------------------
Id Operation Name
-------------------------------------------
0 SELECT STATEMENT
* 1 FILTER
2 TABLE ACCESS FULL EMPLOYEES
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
The "NULL IS NOT NULL" filter is a false constant predicate, that will prevent the table scan from even taking place.
Also in 11gR1, the optimization became available for ANSI compliant joins. For this query:
select employee_name
from employees e inner join jobs j
on e.job_id = j.job_id;
the optimizer can eliminate JOBS and produce this plan:
-------------------------------------------
Id Operation Name
-------------------------------------------
0 SELECT STATEMENT
1 TABLE ACCESS FULL EMPLOYEES
-------------------------------------------
Outer Join Table Elimination
In 11gR1, a new form of table elimination was introduced for outer joins, which does not require PK-FK constraints. For the example, we require a new table and an addition to EMPLOYEES:
create table projects
(
project_id NUMBER UNIQUE,
deadline DATE,
priority NUMBER
);
alter table employees add project_id number;
Now consider a query that outer joins employees and projects:
select e.employee_name, e.project_id
from employees e, projects p
where e.project_id = p.project_id (+);
The outer join guarantees that every row in employees will appear at least once in the result. The unique constraint on projects.project_id guarantees that every row in employees will match at most one row in projects. Together, these two properties guarantee that every row in employees will appear in the result exactly once. Since no other columns from projects are referenced, projects can be eliminated, and the optimizer can choose this plan:
-------------------------------------------
Id Operation Name
-------------------------------------------
0 SELECT STATEMENT
1 TABLE ACCESS FULL EMPLOYEES
-------------------------------------------
Why Would I Ever Write Such a Query?
All of the example queries in this post are very simple, and one would be unlikely to write a query where the join is so obviously unnecessary. There are many real world scenarios where table elimination may be helpful, including machine-generated queries and elimination of tables in views. For example, a set of tables might be exposed as a view, which contains a join. The join may be necessary to retrieve all of the columns exposed by the view. But some users of the view may only access a subset of the columns, and in this case, the joined table can be eliminated.
For example, consider the view:
create view employee_directory_v as
select e.employee_name, d.department_name, j.job_title
from employees e, departments d, jobs j
where e.department_id = d.department_id
and e.job_id = j.job_id;
This view might be exposed to a simple employee directory application. To lookup employee names by job title, the application issues a query:
select employee_name
from employee_directory_v
where department = 'ACCOUNTING';
Since the job_title column is not referenced, jobs can be eliminated from the query, and the optimizer can choose this plan:
--------------------------------------------
Id Operation Name
--------------------------------------------
0 SELECT STATEMENT
* 1 HASH JOIN
2 TABLE ACCESS FULL EMPLOYEES
* 3 TABLE ACCESS FULL DEPARTMENTS
--------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
3 - filter("D"."DEPARTMENT_NAME"='ACCOUNTING')
Known Limitations
There are currently a few limitations of table elimination:
- Multi-column primary key-foreign key constraints are not supported.
- Referring to the join key elsewhere in the query will prevent table elimination. For an inner join, the join keys on each side of the join are equivalent, but if the query contains other references to the join key from the table that could otherwise be eliminated, this prevents elimination. A workaround is to rewrite the query to refer to the join key from the other table (we realize this is not always possible).
As promised, we have the first of the slide decks from the Summit sessions:
Cobbler: Provisioning for Bare Metal and Virtualization
[ODP] slides by Michael DeHaan from his talk given Thursday June 19, 10:15 a.m.
And we still have a few more news items and blog entries about the Summit:
- Barton George (from Sun): Podcast interview with Jim Whitehurst
- Xen proponents question merits of Red Hat KVM hypervisor (Bridget Botelho for SearchServerVirtualization.com)
- IT leaders urged to contribute code to open-source projects (from Esther Schindler at Computerworld)
I spent Monday and Tuesday this week on Velocity Conference It was
quite interesting event worth attending and it was very good to see
the problems in this are going beyond Apache, PHP, Memcache and MySQL.
A lot of talks on this conference was focusing on what is called
“FrontEnd”. The meaning of Frontend is not the frontend web server
commonly used in many architectures but rather optimization on the
client side - how to make a browser to do less requests, make them
parallel, fetch less data and execute client side code faster.
Steve Souders mentioned in his talk for Alexa 10 web sites he examined
typically 80-90% of page response time comes from other things than
fetching HTML of the main page - fetching CSS, JavaScript, Images
which makes it number one thing to focus your optimization on.
I do not fully agree for number of reasons.
First - a lot of people focus on Backend optimization first, have
monitoring and graphing setup based on the main HTML page response
this means quite frequently we speak about sites which have relatively
well optimized backends but have not spent time on the frontend
optimization. In this case not a big surprise there are more low
hanging fruits on frontend part. We often deal with people with
less than optimized backends with some pages (search,reports etc)
taking minutes to load which makes these few seconds you can shave off
by client side optimization secondary.
Second - The stakes for backend optimization are higher. If you do not
optimize your web site on Frontend you will have worse user experience
- users may not engage so actively, leave your site faster or not
convert. This is very important. However if you do not optimize
backend enough to handle your load your web site can simply get
overloaded and die. Remember all these “slashdotted” or
“techcrunched” sites which went down because their back end just was
not ready.
Third - Even if your backend is reasonably fast say you can have under
100ms response time on main HTML page there are still reasons to optimize
it, especially for large scale systems. If you can come up with idea
to provide same response time just with lower hardware requirements
you can save on hardware, power and operations which are considerable
costs for large projects.
I would say instead the same rule as back end optimization applies
here - if you spend 90% time running PHP code and only 10% fetching
data from MySQL database it does not make sense to focus on MySQL
optimization. If 90% is spent on other tasks than fetching your HTML
page all together you surely should focus on it. But get real numbers
for your application before you decide.
Having said that I will now go and read Steve’s book which he kindly
signed for me to get up to speed with front end optimization.
Entry posted by peter | 7 comments
Add to: 
| 
| 
| 
| 
Introduction
- Toad Base – maybe known as Standard (includes Knowledge Xperts + Debugging) - essential Toad features
- Toad Professional (Toad Base + extra features) - focussed on coding best practices
- Toad Xpert (Toad Professional + SQL Optimizer) - focussed on application performance
- Toad Development Suite (Toad Xpert + Quest Code Tester + Benchmark Factory for Oracle) – end-to-end Oracle development solution
- Toad DBA Suite (Toad Xpert + DB Admin Module+ Toad Data Modeler + Benchmark Factory for Oracle + Spotlight on Oracle) – complete DBAs toolkit for administration, diagnostics and tuning
- ADDM/AWR Report Generator – enables snapshot management and shows both AWR and ASH reports
- AWR Browser – graphical representation of data collected from AWR
- ASM Manager – enables management of ASM disk groups and clients
- Which version of Toad you have (e.g. Oracle 10g features only appeared in Toad version 8.0 (July 2004)
- Which Edition of Toad you have (see above)
- Which version of Oracle database you are connected to
- Which options were selected/deselected when/after Oracle database was installed
- What Oracle Role/System Privileges you have granted
Customize is also available in older versions of Toad but the process is a little different.
NOTE: There are features installed by default in the Oracle database which are ready to use, even if the license for such features has not been purchased (e.g. Diagnostics Pack, Tuning Pack). It is, therefore, the end user's responsibility to make sure that these features, which may be used by Toad, are covered by the user's Oracle license. Such features include, but are not limited to, Partitioning, RAC, Advanced Workload Repository (AWR) and Compression.
黑盒和白盒只是一个相对的概念，严格意义上说，并并没有绝对的黑盒和白盒，而且现在也有了灰盒的概念。
看看wiki上对于黑盒白盒的定义吧。
Black box testing takes an external perspective of the test object to derive test cases. These tests can be functional or non-functional, though usually functional. The test designer selects valid and invalid input and determines the correct output. There is no knowledge of the test object’s internal structure.
This method of test design is applicable to all levels of software testing: unit, integration, functional testing, system and acceptance. The higher the level, and hence the bigger and more complex the box, the more one is forced to use black box testing to simplify. While this method can uncover unimplemented parts of the specification, one cannot be sure that all existent paths are tested.
Test design techniques
Typical black box test design techniques include:
* Equivalence partitioning
* Boundary value analysis
* Decision table testing
* Pairwise testing
* State transition tables
* Use case testing
* Cross-functional testing
[edit] Hardware
Functional testing devices like power supplies, amplifiers, and many other simple function electrical devices is common in the electronics industry. Automated functional testing of specified characteristics is used for production testing, and part of design validation.
White box testing
White box testing (a.k.a. clear box testing, glass box testing or structural testing) uses an internal perspective of the system to design test cases based on internal structure. It requires programming skills to identify all paths through the software. The tester chooses test case inputs to exercise paths through the code and determines the appropriate outputs. In electrical hardware testing, every node in a circuit may be probed and measured; an example is in-circuit testing (ICT).
Since the tests are based on the actual implementation, if the implementation changes, the tests probably will need to change, too. For example ICT needs updates if component values change, and needs modified/new fixture if the circuit changes. This adds financial resistance to the change process, thus buggy products may stay buggy. Automated optical inspection (AOI) offers similar component level correctness checking without the cost of ICT fixtures, however changes still require test updates.
Software Testing Portal
While white box testing is applicable at the unit, integration and system levels of the software testing process, it is typically applied to the unit. While it normally tests paths within a unit, it can also test paths between units during integration, and between subsystems during a system level test. Though this method of test design can uncover an overwhelming number of test cases, it might not detect unimplemented parts of the specification or missing requirements, but one can be sure that all paths through the test object are executed.
Typical white box test design techniques include:
* Control flow testing
* Data flow testing
For more information on Control flow testing and Data flow testing click on this link to download pdf.
Last week Yahoo CEO Jerry Yang literally dropped off the grid for a couple of days, leaving his top execs (other than, presumably, President Sue Decker) in the dark. As I wrote on Saturday, no one could locate Yang, and, given the sheer number of high level departures and looming reorganization, those remaining in their jobs were basically freaking out.
As the weekend progressed it seemed clear that anything was possible. More than a few people saw Yang stepping down, with a new CEO stepping in. Other theories (all coming from Yahoo senior ranks) predicted anything from a merger, restructuring, asset sale, etc. Saturday was the low point; fear was rampant.
Then Yang reappeared, with a renewed determination to stay in power, fight off all these new activist shareholders and keep the status quo, say people close to Yahoo. No one seems to know exactly what happened to turn him around, but they say he’s digging in and keeping up the fight to keep Yahoo at least partially independent.
Today’s letter to shareholders was a not-so-subtle way of showing that Yang remains in control of the company, and retains the confidence of his board. And the board of directors also retains confidence in itself, apparently: “Your Current Board of Directors Has the Knowledge, Experience and Commitment to Best Represent Your Interests and Maximize Stockholder Value.”
Microsoft Negotiations Heating Up Again
Microsoft is fighting for Yahoo in two ways - First, they’re denying that any talks are occurring in the hope of keeping Yahoo’s stock price down. This keeps the PR people busy as they field calls and answer direct questions indirectly. Meanwhile, a contingent of Microsoft and Yahoo insiders, desperate to marry these two companies, keep telling us that negotiations are very much alive, even if not officially recognized.
It’s Orwellian, but everyone knows Microsoft and Yahoo are talking, but since they officially aren’t talking, we’re not supposed to report on it. Meanwhile, the discussions go on.
So what kind of deal are they not talking about? It could be a full buyout. Or it could be a partial buyout tied to that fugly search asset acquisition deal Microsoft put on the table after merger talks broke down. One person close to the negotiations pegged a full buyout by end of year at 60% likely.
What about that Google deal? Well, it turns out there’s no penalty at all if Yahoo simply never implements it. Google can terminate the agreement, but there’s no downside to Yahoo. If Yahoo sells itself to someone they have to pay a steep $250 million fee to Google. But an interesting detail: Microsoft can buy up to 35% of Yahoo without triggering that $250 million penalty fee to Google. See the full analysis in our previous post.
Crunch Network: MobileCrunch Mobile Gadgets and Applications, Delivered Daily.
This afternoon at Structure 08 an interesting discussion was had about the birth, growth, trials and tribulations of Salesforce.com. Om Malik from GigaOm was joined by Michael Copeland from Fortune Magazine and Parker Harris from Salesforce.com. While the 'fireside chat' was titled The Endgame for Boxed Software?, the focus was on what lessons can be learned from the venerable CRM vendor.
According to Harris, in the consumer world, everyone must think not only on one level, but on several. Salesforce.com thought about software, scale and the Internet; questioning what it would take to build this 'thing' they were thinking about.
"When we met Marc [Benioff], he had a vision and had written a two page e-mail detailing that we need to make this as easy as buying a book on Amazon,' Harris says. At the time, there was Amazon, Google and E-bay.
In early 1999, they built a prototype. Stealing tabs from Amazon and buttons from Yahoo!, they built a site.
Om: What has the transition been from then and now and why?
Harris: As a technology, we wanted to build a platform first. One that is beautiful from a technology perspective. But you risk losing touch with why you're doing it. Originally it was going to be for sales people and there were various abstract layers; we quickly realized the need to unify them. We continued adding layers in response to our customers; columns, end user interface. At that point we realized it could be a platform; we didn't consciously do that.
When we started, a lot of people said they didn't want to trust their information to anyone; data, privacy, these were all big things, and people were concerned with trusting their customer records and leads with another company. We made the huge step, and now people trust the information services.
It's still very early days in platforms--are you going to use someone else's platform (app engine from Google, Amazon) or use your own?
It's all about the database. Databases have evolved. Today you don't really go into business to write it, you find one. I see platforms heading in the same direction.
We sell to business. We do a lot of work on compliance and security issues to meet the needs of enterprise. For business we are very appropriate; for websites, go to Google. Amazon gives you CPU time or storage. We too have a lot of technology that is pre-built that you can leverage if you want; analytics, search, 24.7 web services, API, it's all there.
Michael: What about compliance and security and is anything Salesforce.com can do to get apps to go viral.
Harris: We want to have a directory where people can go find pages quickly. Free pricing models are one way to drive a viral model. We are looking at ways how to work with Facebook. We are looking to involve developers more.
Om: Infrastructure; what are the challenges
Harris: About two years ago we did go through some serious issues and part of it was due to eBay. They could give us more power but no space, or space and no power - so we moved everything to new data centers.
We made two big mistakes: we changed everything all at once, which is a big risk for any business, and moved onto large scale systems. Pushing scale in a vertical sense created a complexity that was just too hard. Then we hired the guy from eBay to make sure it never happened again.
There is no blueprint on building a scalable service; you never replace it with V2 so you are forever changing it. There are best practices that have evolved, and people should follow them if they want to do this.
Early on we thought of tech innovation as software and infrastructure, and that innovation can occur on one or the other. To truly innovate you need to think of it as one thing and change all areas.
也算中国开源界的一个进步。
http://wiki.woodpecker.org.cn:9081/classes/corepython/
Nicholas:
这本书的翻译可以说历时 3 个月左右吧，判随严格的审校制度，质量得到了保证。在啄木鸟的页面 审校计划 里面可以看到所有的参与者以及参与审校的朋友们，大家付出了很多的心血保证这次翻译的完成，在这份邮件里面，可以看到项目在 12 月出总算胜利结束了。
但是随着翻译结束，关于此书的消息就一直没有下文了，没人知道这本书什么时候会出版，这本书也没有作为 PDF 在网上流传。但目前这本书却已经在市面上开始销售了，并且译者也不再是 CPUG 所熟悉的名字，我大概看了一下 CSDN 在网上提供的示例章节，并且对比 OpenBookProject 中此书的翻译，我的直觉告诉我，这些翻译就是来自 CPUG 的朋友们，没有他们的努力，这本书不可能完成。
但是还是这个译者的问题，列表中的 CorePy 就是宋吉广，为什么他当初匿名参与，不愿意透露自己的身份，再者，为什么出版的时候他就以自己的名字作为译者，将广大参与翻译的 Pythoner 作为了贡献者。虽然我没有参与这次翻译，但是就算作为一个旁观者，我觉得也有必要出来说一下。
我们都是技术工作者，因为喜爱 Python 这门语言互相认识，并且希望能够推广 Python，大家一腔热情翻译的书最后却以别人的名字出版了，难道不觉得很难受吗。更多的还有出版涉及的利益问题，大家花了大力气翻译，但最后出版社和宋吉广倒是占了不少便宜，反而大家什么都没有得到。如果大家不是很在乎这一块，我觉得至少也应该交给 CPUG，作为建设费用，日后可以添置服务器，设备之用。
今天下午发现,支付宝调整了所有支付宝用户的即时到帐限额.具体如下:
原普通用户即时到帐限额为 500 元,调整后为 2000 元.原数字证书用户即时到帐限额为 2000 元,调整后为 10000 元.
与在支付宝、淘宝工作的两位朋友联系未果,关心此消息的朋友可持续关注支付宝社区公告.相关截图如下.
2008-06-25 Wed
2008-06-24 Tue
AnySQL.net
DBA notes
Oracle & Starcraft
eagle's home
Give you some color to see see!
Oracle Scratchpad
Oracle Life
OracleDBA Blog---请享受无法回避的痛苦!
Uploads from dbanotes
Chanel [K]
xzh2000的博客
Oracle Security Blog
ERN空间
Eddie Awad's Blog
MySQL Performance Blog
The Tom Kyte Blog
del.icio.us/fenng/oracle
AIXpert
O'Reilly Databases
Red Hat Magazine
DBASupport
Pythian Group Blog » Log Buffer
车东[Blog^2]
blue_prince
玉面飞龙的BLOG
此生 今世
人生就是如此
Orange Tiger 木匠 的 移民生活
生活帮-LifeBang
Hey!! Sky!
dba on unix
Oracle Notes Wiki
Brotherxiao's Home
柔嘉维则@life.oracle.eng
Fenng's shared items in Google Reader
缥缈游侠-logzgh
Tanel Poder's blog: Core IT for geeks and pros
DBA Tools
ilonng
yangtingkun
NinGoo@Net
Oracle & Unix
Inside the Oracle Optimizer - Removing the black magic
Ricky's Test Blog
DBA@Taobao
存储部落
Think in 88
Alibaba DBA Team
Oracle Team @SNC
OracleBlog.cn